﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TalentPools.Model;

namespace TalentPools.DAL
{
    public class Person_LoginService
    {
        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteLoginInfo(int id)
        { 
            string sql="delete from Person_Login where id=@id";
            SqlParameter para = new SqlParameter("@id", id);
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, para) > 0;
        }
        /// <summary>
        /// 更改状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public bool UpdateLoginInfo(int id,bool state)
        {
            string sql = "update Person_Login set state=@state where id=@id";
            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@state",state),
            new SqlParameter("@id",id)
            };
            return (int)DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras) > 0;
        }
        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataSet GetLoginInfoPage(int pageIndex, int pageSize, string where)
        {
            string sql = "select * from(select ROW_NUMBER() over(order by RegTime Desc)RowIndex,* from Person_Login where UserName like '%'+@where+'%')a where RowIndex between @begin and @end";
            SqlParameter[] paras = new SqlParameter[] {
            new SqlParameter("@begin",pageSize*(pageIndex-1)+1),
            new SqlParameter("@end",pageIndex*pageSize),
            new SqlParameter("@where",where)
            };
            using (DataSet ds=DBHelper.ExecuteDataSet(CommandType.Text,sql,paras))
            {
                return ds;
            }

        }
        /// <summary>
        /// 根据条件查询记录数
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public int GetLoginInfoCount(string where)
        {
            string sql = "select count(*) from Person_Login where username like '%'+@where+'%'";
            SqlParameter para = new SqlParameter("@where", where);
            return (int)DBHelper.ExecuteScalar(CommandType.Text, sql, para);
        }
        public int CheckUsername(string username)
        {
            string sql = "select count(1) from Person_Login where Username=@username";
            SqlParameter para = new SqlParameter("@username", username);
            return (int)DBHelper.ExecuteScalar(CommandType.Text, sql, para);
        }
        /// <summary>
        /// 根据用户名和密码查询用户数
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public int UserCountByLoginInfo(string userName, string password)
        {
            string sql = "select count(*) from Person_Login where UserName=@UserName and UserPwd=@password";
            SqlParameter[] paras = new SqlParameter[] { 
            new SqlParameter("@UserName",userName),
            new SqlParameter("@password",password)
            };
            return (int)DBHelper.ExecuteScalar(CommandType.Text, sql, paras);
        }

        public string GetPersonState(string username)
        {
            string sql = "select state from person_login where username='" + username + "'";
            return DBHelper.ExecuteScalar(CommandType.Text, sql, null).ToString();
        }
        /// <summary>
        /// 插入
        /// </summary>
        /// <param name="login"></param>
        /// <returns></returns>
        public bool InsertLoginInfo(Person_Login login)
        {
            string sql = "insert into Person_Login(UserName,UserPwd,RegTime,Question,Answer,IpAdd,Email,state)values(@UserName,@UserPwd,@RegTime,@Question,@Answer,@IpAdd,@Email,@state)";
            SqlParameter[] paras = new SqlParameter[] { 
            new SqlParameter("@UserName",login.UserName),
            new SqlParameter("@UserPwd",login.UserPwd),
            new SqlParameter("@RegTime",login.RegTime),
            new SqlParameter("@Question",login.Question),
            new SqlParameter("@Answer",login.Answer),
            new SqlParameter("@IpAdd",login.IpAdd),
            new SqlParameter("@Email",login.Email),
            new SqlParameter("@state",login.state)
            };
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras) == 1;
        }
        #region 修改密码
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool EditPwd(string username, string password)
        {
            string sql = "Update Person_Login set UserPwd='"+password+"' where UserName='" + username + "'";
            bool b= DBHelper.ExecuteNonQuery(CommandType.Text, sql, null) > 0;
            return b;
        }

        #endregion

        #region 查询密码
        public string CheckPwd(string userName)
        {
            string sql = "select UserPwd from Person_Login where UserName=@UserName";
            SqlParameter para = new SqlParameter("@UserName", userName);
            return DBHelper.ExecuteScalar(CommandType.Text, sql, para).ToString();
        }
        #endregion
    }
}
